package com.example.mapper;

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.example.entity.StudentHealth;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Select;

import java.util.List;

@Mapper
public interface StudentHealthMapper extends BaseMapper<StudentHealth> {
    
    /**
     * 获取学生的健康记录列表（包含学生信息）
     * @param studentId 学生ID
     * @return 健康记录列表
     */
    @Select("SELECT sh.*, s.name as student_name, s.gender as student_gender, s.age as student_age " +
            "FROM student_health sh " +
            "LEFT JOIN student s ON sh.student_id = s.id " +
            "WHERE sh.student_id = #{studentId} AND sh.deleted = 0 " +
            "ORDER BY sh.record_time DESC")
    List<StudentHealth> selectHealthRecordsWithStudentInfo(Long studentId);
    
    /**
     * 获取学生的最新健康记录（包含学生信息）
     * @param studentId 学生ID
     * @return 健康记录
     */
    @Select("SELECT sh.*, s.name as student_name, s.gender as student_gender, s.age as student_age " +
            "FROM student_health sh " +
            "LEFT JOIN student s ON sh.student_id = s.id " +
            "WHERE sh.student_id = #{studentId} AND sh.deleted = 0 " +
            "ORDER BY sh.record_time DESC LIMIT 1")
    StudentHealth selectLatestHealthRecordWithStudentInfo(Long studentId);
    
    /**
     * 获取学生的健康趋势数据
     * @param studentId 学生ID
     * @return 健康趋势数据
     */
    @Select("SELECT " +
            "record_time, " +
            "height, " +
            "weight, " +
            "bmi, " +
            "health_status " +
            "FROM student_health " +
            "WHERE student_id = #{studentId} AND deleted = 0 " +
            "ORDER BY record_time ASC")
    List<StudentHealth> selectHealthTrend(Long studentId);
    
    /**
     * 获取班级的健康统计信息
     * @param classId 班级ID
     * @return 健康统计信息
     */
    @Select("SELECT " +
            "COUNT(DISTINCT sh.student_id) as total_students, " +
            "AVG(sh.height) as average_height, " +
            "AVG(sh.weight) as average_weight, " +
            "AVG(sh.bmi) as average_bmi, " +
            "COUNT(CASE WHEN sh.bmi < 18.5 THEN 1 END) as underweight_count, " +
            "COUNT(CASE WHEN sh.bmi >= 18.5 AND sh.bmi < 24 THEN 1 END) as normal_weight_count, " +
            "COUNT(CASE WHEN sh.bmi >= 24 AND sh.bmi < 28 THEN 1 END) as overweight_count, " +
            "COUNT(CASE WHEN sh.bmi >= 28 THEN 1 END) as obese_count " +
            "FROM student_health sh " +
            "LEFT JOIN student s ON sh.student_id = s.id " +
            "WHERE s.class_id = #{classId} AND sh.deleted = 0 " +
            "AND sh.record_time = (SELECT MAX(record_time) FROM student_health WHERE student_id = sh.student_id)")
    StudentHealth selectClassHealthStatistics(Long classId);
} 